import akshare as ak
import pandas as pd
import psycopg2
import datetime
import sys
import time


def get_daily_hist(
    code: str,
    type: str,
    index: int,
    start_date: str,
    end_date: str = "20500101",
) -> pd.DataFrame:
    """
    :type: 是沪，深，京
    :index: % 用东财，同花顺，新浪,网易，腾讯，百度
    """
    #东财
    return ak.stock_zh_a_hist(code, "daily", start_date, end_date, adjust="qfq")

# #新浪
# return ak.stock_zh_a_daily(type+code, start_date, end_date, adjust="qfq")


insert_sql = '''INSERT INTO stock_datas.stock_qfq_{}(
	date, open, close, hight, low, volume, volume_quota, vibration, rise_fall_rate, rise_fall_quota, turnover_rate)
	VALUES ('{}', {}, {}, {}, {}, {}, {}, {}, {}, {}, {});'''


def insert_data_to_db(
        code: str,
        # cur: psycopg2.cursor,
        # conn: psycopg2.connection,
        df: pd.DataFrame) -> None:
    try:
        for item in df.values:
            exec_sql = insert_sql.format(code,
                                         item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7], item[8], item[9], item[10])
            cur.execute(exec_sql)
        conn.commit()
    except Exception as e:
        print("!!!!!!!! ", e)
        sys.exit(0)
    

conn = psycopg2.connect(database="finance",
                        user="postgres", password="123456",
                        host="127.0.0.1", port="5432")
cur = conn.cursor()  # 创建指针对象

cur.execute("SELECT code, sname FROM stock_market.stock")
datas = cur.fetchall()
for item in datas:
    # time.sleep(0.05)
    code = item[0]
    sname = item[1]
    if "ST" in sname:
        print("not create %s", sname)
        continue

    select_sql = '''SELECT id,date FROM stock_datas.stock_qfq_{} ORDER BY date DESC LIMIT {}'''
    sql = select_sql.format(code, 1)
    cur.execute(sql)
    oneData = cur.fetchall()
    if len(oneData) <= 0:
        print("=========== code", code, "can't find data")
        continue
    lastDate = oneData[0][1]    
    deltaday =datetime.date.today() - lastDate 
    if deltaday.days <= 0 :
        continue
    lastDate = lastDate+datetime.timedelta(days=1)
    beginDate=lastDate.strftime("%Y%m%d")
    print("get stock info", code, sname, beginDate)
    df = get_daily_hist(code, "sz", 1, beginDate)
    insert_data_to_db(code, df)
